﻿// =================================================================== 
// 项目说明
//====================================================================
// 文件： ERP_StockInfo.cs
// 创建时间：2016/11/29
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CRMMis.Model;
using CRMMis.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  库存总表.
    /// </summary>
    public partial class ERP_StockInfoDAL : IERP_StockInfoDAL
    {
		//此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取ERP_StockInfo表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的

		private const string QueryDataString = @"SELECT A.CargoName,A.CargoCode,A.Spec,A.Version,
                    A.Area,A.ProduceName,B.Supplyprice,SQL1.*,SQL1.Count*B.Supplyprice StockPrice,
                    C.STOREROOM_NAME FROM ERP_Cargo A
                    LEFT JOIN ERP_Cargo_Detail B ON A.CargoId=B.CargoId
                    INNER JOIN (SELECT A.StoreId,A.DetailId, B.Count, A.StoreRoomId, B.BatchNo
                    FROM ERP_StockInfo A INNER JOIN ERP_StockDetail B ON A.StoreId= B.StoreId
                    WHERE B.Count>0) SQL1 ON B.DetailId=SQL1.DetailId
                    LEFT JOIN VIEW_STORE C ON SQL1.StoreRoomId=C.STOREROOM_ID";

        public DataSet GetStockList(string sWhere)
        {
            string sqlStr = @"SELECT CargoName,CargoCode,Spec,Version,Area,ProduceName,
                Supplyprice,DetailId,SUM(SQLA.Count) Count,
                SUM(SQLA.Count)*SQLA.Supplyprice StockPrice,STOREROOM_NAME FROM 
                (" + QueryDataString+ ") SQLA ";

            if (sWhere.Length > 0)
                sqlStr += " where " + sWhere+ " GROUP BY CargoName,CargoCode,Spec,Version,Area,ProduceName,Supplyprice,SQLA.DetailId,STOREROOM_NAME";

            WriteLog("ERP_StockInfoDAL", "GetStock(string sWhere)", sqlStr);

            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }

        public DataSet GetStockList(int iPageSize, int iPageIndex, string sWhere, string sOder)
        {
            sOder = "CargoName,Spec,Version,Area,ProduceName";

            string TableString2 = @"SELECT CargoName,CargoCode,Spec,Version,Area,ProduceName,
                Supplyprice,DetailId,SUM(SQLA1.Count) Count,
                SUM(SQLA1.Count)*SQLA1.Supplyprice StockPrice,STOREROOM_NAME FROM 
                (" + QueryDataString + ") SQLA1 ";

            if (sWhere.Length > 0)
                TableString2 += " where " + sWhere + " GROUP BY CargoName,CargoCode,Spec,Version,Area,ProduceName,Supplyprice,SQLA1.DetailId,STOREROOM_NAME";

            TableString2 = "(" + TableString2 + ") SQLA";

            string sqlStr = "select * from (select ROW_NUMBER() OVER(order by " + sOder + " ) as row, * from " + TableString2;

            sqlStr += ") SQL1 where row between " + Convert.ToString(iPageSize * (iPageIndex - 1) + 1) + " and " + Convert.ToString(iPageSize * iPageIndex);

            WriteLog("ERP_StockInfoDAL", "GetStockList(int iPageSize,int iPageIndex, string sWhere, string sOder)", sqlStr);

            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }
    }
}
